﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class EnvironmentController : BaseController
	{
		public ActionResult Index ()
		{
            EnvironmentIndexViewModel vm = new EnvironmentIndexViewModel();

            vm.Environments = EnvironmentController.GetEnvironmentCount();

			return View (vm);
		}

        public ActionResult List(int? id)
        {
            EnvironmentListViewModel vm = new EnvironmentListViewModel();

            vm.ProjectId = id;
            vm.EnvironmentList = EnvironmentController.GetEnvironmentList(id);

            return this.List(vm);
        }

        public ActionResult Resource(int? id)
        {
            etracks.Web.EnvironmentResourceViewModel vm = new EnvironmentResourceViewModel();

            etracks.EntityModels.Environment e = EnvironmentController.GetEnvironment(id.Value);

            vm = new EnvironmentResourceViewModel
            {
                Id = e.Id, 
                Name = e.Name,
                ProjectId = e.ProjectId
            };

            vm.ResourceList = EnvironmentController.GetResourceList(id.Value);

            return View(vm);
        }

        public ActionResult LinkResource(int? id)
        {
            etracks.Web.EnvironmentResourceViewModel vm = new EnvironmentResourceViewModel();

            //etracks.EntityModels.Environment e = EnvironmentController.GetEnvironment(id.Value);

            //vm = new EnvironmentResourceViewModel
            //{
            //    Id = e.Id,
            //    Name = e.Name,
            //    ProjectId = e.ProjectId
            //};

            //vm.ResourceList = EnvironmentController.GetResourceList(id.Value);

            return View(vm);
        }

        public ActionResult View(int id)
        {
            EnvironmentReadOnlyViewModel vm = GetReadOnlyEnvironment(id);

            vm.ResourceList = GetResourceList(id);

            return View(vm);
        }

        public ActionResult Edit(int id)
        {
            EnvironmentRegistrationViewModel vm = new EnvironmentRegistrationViewModel();

            etracks.EntityModels.Environment p = GetEnvironment(id);

            if (p != null)
            {
                vm = new EnvironmentRegistrationViewModel
                {
                    Id = p.Id,
                    Name = p.Name,
                    ProjectId = p.ProjectId
                };
            }

            // If user's has "change environment project" feature
            bool haveChangeEnvironmentProjectFeature = false;

            if (haveChangeEnvironmentProjectFeature)
                vm.ProjectList = EnvironmentController.GetProjectList();
            else
                vm.ProjectList = EnvironmentController.GetProjectList().Where(r => r.Id == vm.ProjectId).ToList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Edit(EnvironmentRegistrationViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string,object>();
                parameters.Add("id", vm.Id);
                parameters.Add("name", vm.Name);
                parameters.Add("project_id", vm.ProjectId);
                
                int rows_affected = PostgresHelper.Execute("UPDATE environment SET name = :name, project_id = :project_id WHERE id = :id;", parameters);

                if (rows_affected > 0)
                    this.SuccessAlert("Environment updated.");

            }
            catch (Exception)
            {
                throw;
            }

            vm.ProjectList = EnvironmentController.GetProjectList();
            
            return View(vm);
        }

        [HttpPost]
        public ActionResult Delete(int id)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add("id", id);

                int rows_affected = PostgresHelper.Execute("DELETE FROM environment WHERE id = :id;", parameters);

                if (rows_affected > 0)
                    this.SuccessAlert("Environment deleted.");

                return RedirectToAction("List", "Environment");
            }
            catch (Exception)
            {
                throw;
            }

        }

        private ActionResult List(EnvironmentListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            EnvironmentSearchViewModel vm = new EnvironmentSearchViewModel();

            vm.Search_Term = search_term;

            vm.EnvironmentList = EnvironmentController.EnvironmentSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Add(int? id)
        {
            EnvironmentRegistrationViewModel vm = new etracks.Web.EnvironmentRegistrationViewModel();

            if (id.HasValue)
            {
                vm.ProjectId = id.Value;
                vm.ProjectList = EnvironmentController.GetProjectList().Where(r => r.Id == id.Value).ToList();
            }
            else
            {
                vm.ProjectList = EnvironmentController.GetProjectList();
            }

            return View(vm);
        }

        [HttpPost]
        public ActionResult Add(EnvironmentRegistrationViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Name))
                ModelState.AddModelError("Name", "Cannot be empty.");

            if (ModelState.IsValid)
            {
                string new_id = RegisterEnvironment(vm.Name, vm.ProjectId);

                this.SuccessAlert("Environment added");

                return RedirectToAction("Edit", "Environment", new { id = new_id });

            }

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetEnvironmentCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM Environment;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static etracks.EntityModels.Environment GetEnvironment(int id)
        {
            //List<Environment> result = new List<Environment>();
            etracks.EntityModels.Environment result = new etracks.EntityModels.Environment();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM Environment WHERE id = :id ORDER BY id LIMIT 1;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new etracks.EntityModels.Environment
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    ProjectId = n.Field<int?>("project_id")
                }).FirstOrDefault();
            }

            return result;
        }

        private static EnvironmentReadOnlyViewModel GetReadOnlyEnvironment(int id)
        {
            EnvironmentReadOnlyViewModel result = new EnvironmentReadOnlyViewModel();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT * FROM environment WHERE id = :id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new EnvironmentReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    ProjectId = n.Field<int?>("project_id")
                }).FirstOrDefault();
            }

            return result;
        }

        private static List<EnvironmentListItem> GetEnvironmentList(int? id)
        {
            List<EnvironmentListItem> result = new List<EnvironmentListItem>();

            string whereClause = string.Empty;

            Dictionary<string, object> parameters = new Dictionary<string, object>();

            if (id.HasValue)
            {
                whereClause = string.Format("WHERE e.project_id = :project_id");
                parameters.Add("project_id", id.Value);
            }

            DataTable dt = PostgresHelper.GetDataTable(string.Format(@"SELECT  e.*, 
        p.name  AS ""project_name"", 
        p.alias AS ""project_alias"", 
        pt.name AS ""project_type_name""
FROM    environment e
LEFT OUTER JOIN 
        project p
        ON e.project_id = p.id
LEFT OUTER JOIN 
        project_type pt
        ON p.project_type_id = pt.id
{0}
ORDER BY
        e.id;", whereClause), parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new EnvironmentListItem
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"), 
                    ProjectId = n.Field<int>("project_id"),
                    ProjectName = n.Field<string>("project_name"),
                    ProjectAlias = n.Field<string>("project_alias"),
                    ProjectTypeName = n.Field<string>("project_type_name")
                }).ToList();
            }

            return result;
        }

        private static List<etracks.EntityModels.Environment> EnvironmentSearchResultsList(string search_term)
        {
            List<etracks.EntityModels.Environment> result = new List<etracks.EntityModels.Environment>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  p.*, pt.name AS ""Environment_type_name""
FROM    Environment p
LEFT OUTER JOIN
        Environment_type pt
        ON p.Environment_type_id = pt.id
WHERE   lower(p.name) LIKE lower(:search_term) 
        OR lower(p.alias) LIKE lower(:search_term)
        OR lower(pt.name) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new etracks.EntityModels.Environment
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name")
                }).ToList();
            }

            return result;
        }

        private static string RegisterEnvironment(string name, int? projectId)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("name", name);
            if (projectId.HasValue)
                parameters.Add("project_id", projectId.Value);
            else
                parameters.Add("project_id", DBNull.Value);
            
            string sql = "INSERT INTO Environment (name, project_id) VALUES (:name, :project_id) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }

        private static List<ResourceReadOnlyViewModel> GetResourceList(int id)
        {
            List<ResourceReadOnlyViewModel> result = new List<ResourceReadOnlyViewModel>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("environment_id", id);

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  r.*, rt.name AS ""resource_name""
FROM    environment_resource er
INNER JOIN
        resource r
        ON er.resource_id = r.id
INNER JOIN
        resource_type rt
        ON r.resource_type_id = rt.id
WHERE   er.environment_id = :environment_id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ResourceReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ResourceTypeId = n.Field<int?>("resource_type_id"),
                    ResourceType = n.Field<string>("resource_name")
                }).ToList();
            }

            return result;  
        }

        private static List<Project> GetProjectList()
        {
            List<Project> result = new List<Project>();

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT * FROM project ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Project
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ProjectTypeId = n.Field<int?>("project_type_id")
                }).ToList();
            }

            return result;
        }
	}
}